package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.security.Permission;
import cn.edu.sdjzu.xg.bysj.security.Role;
import cn.edu.sdjzu.xg.bysj.service.RoleService;
import cn.edu.sdjzu.xg.bysj.util.Condition;
import cn.edu.sdjzu.xg.bysj.util.JdbcHelper;
import cn.edu.sdjzu.xg.bysj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public final class PermissionDao {
    private static PermissionDao permissionDao = null;

    //将唯一构造器私有，阻止本类外生成本类的对象
    private PermissionDao() {
    }

    //返回本类唯一的对象
    public static PermissionDao getInstance() {
        // 静态变量permissionDao为空时，才创建PermissionDao对象。
        // 第二次调用本方法permissionDao就不空了，不再创建对象。
        if (permissionDao == null) {
            permissionDao = new PermissionDao();
        }
        return permissionDao;
    }

    /**
     * 返回id对应的Permission对象
     * @param id 对象对应的id
     * @return Permission对象
     * @throws SQLException
     */
    public Permission find(Connection connection, Integer id) throws SQLException {
        //创建Permission类型的引用，暂不创建对象
        Permission desiredPermission = null;
        String sql_select_id = "SELECT * FROM permission WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()) {
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String url = resultSet_select_id.getString("url");
            String remarks = resultSet_select_id.getString("remarks");
            int roleId = resultSet_select_id.getInt("role_id");
            Role role = RoleService.getInstance().find(roleId);
            desiredPermission = new Permission(id, description, url, no, role);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredPermission;
    }

    public Collection<Permission> findAll(Connection connection, Integer roleId)
            throws SQLException {
        Collection<Permission> desiredPermissions = new TreeSet<Permission>();
        String sql_select_role_id = "SELECT * FROM permission WHERE role_id = ?";
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select_role_id);
        pstmt_select.setInt(1, roleId);
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String description = resultSet_select.getString("description");
            String no = resultSet_select.getString("no");
            String url = resultSet_select.getString("url");
            //会造成循环调用
//            Role role = RoleService.getInstance().find(roleId);
            Permission desiredPermission = new Permission(id, description, url, no, null);
            desiredPermissions.add(desiredPermission);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredPermissions;
    }

    /**
     * 返回某页上的Permission对象
     *
     * @param pagination 分页对象(页号, 每页面对象数)
     * @return Permission对象集合
     * @throws SQLException
     */
    public Collection<Permission> findAll(Connection connection, Pagination pagination, List<Condition> conditionList)
            throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Permission对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<Permission> desiredPermissions = new TreeSet<Permission>();

        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM Permission_bak ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Permission_bak");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }

        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select.next()) {
            //获得permission表中当前记录的id字段的值
            int id = resultSet_select.getInt("id");
            String description = resultSet_select.getString("description");
            String no = resultSet_select.getString("no");
            String url = resultSet_select.getString("url");
            String remarks = resultSet_select.getString("remarks");
            int roleId = resultSet_select.getInt("role_id");
            Role role = RoleService.getInstance().find(roleId);
            //以当前记录的各个字段值为参数，创建Permission对象
            Permission desiredPermission = new Permission(id, description, url, no, role);
            //将当前记录代表的Permission对象添加到desiredPermissions中
            desiredPermissions.add(desiredPermission);
        }
        JdbcHelper.close(resultSet_select, pstmt_select_all);
        return desiredPermissions;
    }

    public boolean update(Connection connection, Permission permission)
            throws SQLException {
        //准备预编译的语句
        String sql_update = "UPDATE permission SET description = ?, url = ?, " +
                "no = ? WHERE id = ?";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_update);
        pstmt_update.setString(1, permission.getDescription());
        pstmt_update.setString(2, permission.getUrl());
        pstmt_update.setString(3, permission.getNo());
        pstmt_update.setInt(4, permission.getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update);
        return rowAffected > 0;
    }

    public boolean add(Connection connection, Permission permission) throws SQLException {
        //获取数据库连接
//        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_insert = "INSERT INTO permission (description, url, no, role_id) VALUES (?, ?, ?, ?)";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_insert);

        pstmt_update.setString(1, permission.getDescription());
        pstmt_update.setString(2, permission.getUrl());
        pstmt_update.setString(3, permission.getNo());
        pstmt_update.setInt(4, permission.getRole().getId());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update, connection);
        return rowAffected > 0;
    }

    public boolean delete(Connection connection, Integer id) throws SQLException {
        //准备预编译的语句
        String sql_delete_id = "DELETE FROM permission WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);

        pstmt_delete.setInt(1, id);

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_delete.executeUpdate();

        JdbcHelper.close(pstmt_delete);
        return rowAffected > 0;
    }
}
